package com.decent.ejfadmin.report.dao.provider;

import java.util.HashMap;

/**
 * 给易服务提供的库存统计
 * @author weicx
 * @date 2019/03/12
 */
public class CollectCardApiStockReportDaoProvider {
    /**
     * 收卡卡密库存统计
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardStockReport(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT " +
                " productType, " +
                " COUNT(1) amount " +
                "FROM " +
                " yuhuang_supply_card " +
                "WHERE " +
                " startTime >= date_sub(now(), INTERVAL 7 DAY)  " +
                "AND status IN (0, 3, 7, 8) group by productType");
        return sql.toString();
    }

    /**
     * 收卡卡密库存面值省份统计
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardFaceReport(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT " +
                " t.area province, " +
                " t.face faceValue, " +
                " COUNT(1) amount " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "  IF (supplyArea = 0, '全国', area) area, " +
                "  face, " +
                "  productType " +
                " FROM " +
                "  yuhuang_supply_card " +
                " WHERE " +
                "  startTime >= date_sub(now(), INTERVAL 7 DAY) " +
                " AND STATUS IN (0, 3, 7, 8) " +
                " AND productType = #{productType} " +
                " ) t " +
                "GROUP BY " +
                " t.area, " +
                " t.face ");
        return sql.toString();
    }


    /**
     * 分页查询收卡库存折扣统计
     *
     * @param params 参数
     * @return sql
     */
    public String collectCardPriceReport(HashMap<String, Object> params) {
        StringBuffer sql = new StringBuffer(100);
        sql.append("SELECT " +
                " t.face faceValue, " +
                "t.userPrice discount, " +
                " COUNT(1) amount " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "  IF (supplyArea = 0, '全国', area) area, " +
                "  face, " +
                "userPrice, " +
                "  productType " +
                " FROM " +
                "  yuhuang_supply_card " +
                " WHERE " +
                "  startTime >= date_sub(now(), INTERVAL 7 DAY) " +
                " AND STATUS IN (0, 3, 7, 8) " +
                " AND productType = #{productType} " +
                "AND face=#{faceValue} " +
                " ) t " +
                "WHERE area=#{province} " +
                "GROUP BY " +
                " t.area, " +
                " t.face," +
                " t.userPrice ");
        return sql.toString();
    }


}
